<?php
namespace Poi\EttsBundle\Model;

/**
 * @author Ricardo J. Vazquez
 * 
 * Representation of a Ticket object from the database
 */
class Ticket extends Model
{
	public $TicketNumber = '';
	public $Latitude = 0.0;
	public $Longitude = 0.0;
	public $Time = '';
	public $InfractionKind = '';
	public $Fine = 0.0;
	public $Article_Number = '';
	public $Point = 0;
	public $Type = '';
	public $Officer = ''; 
	public $Owner = ''; 
	public $Observations = array();
	
	public function init(){
		if(!empty($this->id)){
			$this->TicketNumber = $this->id;
		}
	}
	
	protected function prepareLoadQuery(){
		if(!empty($this->TicketNumber)){
			
			$sql = "SELECT
						VT.TicketNumber,
						VT.BadgeNumber AS 'Officer',
						VT.VIN AS 'Owner',
						'vehicle' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						VT.Longitude,
						VT.Latitude,
						VT.Time
					FROM VehicleTicket VT
						INNER JOIN TicketType TT ON VT.TicketType=TT.TicketType
					WHERE VT.TicketNumber = :key
					UNION SELECT 
						DT.TicketNumber,
						DT.BadgeNumber AS 'Officer',
						DT.LicenseNumber AS 'Owner',	
						'driver' AS 'Type',
						TT.InfractionKind,
						TT.Fine,
						TT.Point,
						TT.Article_Number,
						DT.Longitude,
						DT.Latitude,
						DT.Time
					FROM DriverTicket DT
						INNER JOIN TicketType TT ON DT.TicketType=TT.TicketType
					WHERE DT.TicketNumber = :key";

			$this->loadStmt = $this->conn->prepare($sql);

			$this->loadStmt->bindValue("key", $this->TicketNumber);
		}
	}
	
	/**
	* Returns the Observations property, loading if necesary
	*
	* @return array with observations
	*/
	public function getObservations(){
		if(!$this->isLoaded){
			$this->load();
		}
		if(!empty($this->TicketNumber) && $this->Type = 'driver'){
			
		
			$sql = "SELECT 
						OT.Observations
					FROM Observations O
						INNER JOIN ObservationsType OT ON O.ObservationsType=OT.idObservationsType
					WHERE O.TicketNumber = :key";

			$loadStmt = $this->conn->prepare($sql);

			$loadStmt->bindValue("key", $this->TicketNumber);
						
			$loadStmt->execute();
			$this->Observations = $loadStmt->fetchAll( \PDO::FETCH_NUM );
		}
		return $this->Observations;	
	}
	
	/**
	* Sets the Observations property
	*
	* @param $obs array or string with the observations
	*/
	public function setObservations($obs){
		if(!is_array($obs)){
			$obs = explode(',',$obs);
		}
		
		//Validation?
				
		$this->Observations = $obs;
	}
	
	/**
	* Updates, by adding to the update buffer, the Observations property
	*
	* @param $obs array or string with the observations
	*/
	public function updObservations($obs){
		if(!is_array($obs)){
			$obs = explode(',',$obs);
		}		
		//Validation?
		
		$this->updateBuffer['Observations'] = $obs;
	}
		
	public function prepareUpdateQuery(){
		if(!empty($this->TicketNumber)){
			if(!$this->isLoaded){
				$this->load();
			}
			
			if($this->Type == 'driver'){
				$sql = "UPDATE DriverTicket
				    	SET 
							BadgeNumber = :officer, 
							Longitude = :longitude,
							Latitude = :latitude,
							Time = :time,
							LicenseNumber = :owner,
							TicketType = :type
				    	WHERE TicketNumber = :key";
			}
			elseif($this->Type == 'vehicle'){
				$sql = "UPDATE VehicleTicket
				    	SET 
							BadgeNumber = :officer, 
							Longitude = :longitude,
							Latitude = :latitude,
							Time = :time,
							VIN = :owner,
							TicketType = :type
				    	WHERE TicketNumber = :key";
			}

			
			$this->updateStmt = $this->conn->prepare($sql);
			$this->updateStmt->bindValue("key", $this->TicketNumber);
						
			//Need to fix checking of emptiness
			( !array_key_exists('Officer',$this->updateBuffer) ? 
			$this->updateStmt->bindValue("officer", $this->Officer) : 
			$this->updateStmt->bindValue("officer", $this->updateBuffer['Officer']) );
			
			( !array_key_exists('Owner',$this->updateBuffer) ? 
			$this->updateStmt->bindValue("owner", $this->Owner) : 
			$this->updateStmt->bindValue("owner", $this->updateBuffer['Owner']) );
			
			( !array_key_exists('Time',$this->updateBuffer)  ? 
			$this->updateStmt->bindValue("time", $this->Time) : 
			$this->updateStmt->bindValue("time", $this->updateBuffer['Time']) );
			
			( !array_key_exists('Longitude',$this->updateBuffer) ? 
			$this->updateStmt->bindValue("longitude", $this->Longitude) : 
			$this->updateStmt->bindValue("longitude", $this->updateBuffer['Longitude']) );
			
			( !array_key_exists('Latitude',$this->updateBuffer) ? 
			$this->updateStmt->bindValue("latitude", $this->Latitude) : 
			$this->updateStmt->bindValue("latitude", $this->updateBuffer['Latitude']) );
			
			//Load type of ticket
			if(array_key_exists('InfractionKind',$this->updateBuffer) ){
				$tempSQL = "SELECT 
								TT.TicketType
							FROM TicketType TT
							WHERE TT.InfractionKind = :key";

				$tempStmt = $this->conn->prepare($tempSQL);
				$tempStmt->bindValue("key", $this->updateBuffer['InfractionKind']);
				$tempStmt->execute();
				$tempStmt->getWrappedStatement()->setFetchMode(\PDO::FETCH_OBJ);
				$TicketType = $tempStmt->fetch(\PDO::FETCH_OBJ);		
				$TicketType = $TicketType->TicketType;
				
				$this->updateStmt->bindValue("type", $TicketType);
			}
			else{
				$tempSQL = "SELECT 
								TT.TicketType
							FROM TicketType TT
							WHERE TT.InfractionKind = :key";

				$tempStmt = $this->conn->prepare($tempSQL);
				$tempStmt->bindValue("key", $this->InfractionKind);
				$tempStmt->execute();
				$tempStmt->getWrappedStatement()->setFetchMode(\PDO::FETCH_OBJ);
				$TicketType = $tempStmt->fetch(\PDO::FETCH_OBJ);		
				$TicketType = $TicketType->TicketType;
				
				$this->updateStmt->bindValue("type", $TicketType);
			}

		}
	}
	
	/**
	* Method to be called in update() to update observations in the database
	*/
	public function updateObservations(){
		if(array_key_exists('Observations',$this->updateBuffer) ){
			
			//Load observation ids.	
			$sql = "SELECT 
						idObservationsType
					FROM ObservationsType ";

			foreach($this->updateBuffer['Observations'] as $key=>$oID){
				if($key == 0){
					$sql .= " WHERE Observations = :obs$key ";
				}
				else {
					$sql .= "";
				}
				$sql .= " OR Observations = :obs$key ";
			}

			$loadStmt = $this->conn->prepare($sql);

			foreach($this->updateBuffer['Observations'] as $key=>$oID){
				$loadStmt->bindValue("obs$key", $oID);
			}

			$loadStmt->execute();

			$obsIds = $loadStmt->fetchAll(\PDO::FETCH_NUM);
			
			//DELETE old ones
			$this->deleteObservations();
			
			//INSERT new ones
			$sql = "INSERT INTO Observations
					(TicketNumber, ObservationsType)
					VALUES ";

			foreach($obsIds as $key=>$oID){
				if($key == 0){
					$sql .= " (:ticket,:obsID$key) ";
				}
				else {
					$sql .= ", (:ticket,:obsID$key) ";
				}
			}

			$createStmt = $this->conn->prepare($sql);

			$createStmt->bindValue("ticket", $this->TicketNumber);
			foreach($obsIds as $key=>$oID){			
				$createStmt->bindValue("obsID$key", $oID[0]);
			}			
						
			$createStmt->execute();	
		}
	}

	/**
	* Overwrites parent method only to call updateObservations
	*/
	public function update(){
		if(is_null($this->updateStmt)){
			$this->prepareUpdateQuery();
		}
		
		$this->conn->beginTransaction();
		try{
		    // do stuff
			$this->updateStmt->execute();
			
			//Type = driver
			if($this->Type == 'driver'){
				$this->updateObservations();
			}

		    $this->conn->commit();
			
			foreach($this->updateBuffer as $property=>$value){
				$method = "set$property";
				$this->$method($value);
			}
			
			return $this->sendAck('Updated succesfully!');
			
		} catch(\Exception $e) {
		    $this->conn->rollback();
		
			//return error message
		    return $this->sendErr('Something went wrong: ' . $e->getMessage());
		}
	}
	
	public function prepareCreateQuery(){
		if(empty($this->TicketNumber)){
			//Generate TicketNumber
		}
		//Load type of ticket
		$tempSQL = "SELECT 
						TT.TicketType
					FROM TicketType TT
					WHERE TT.InfractionKind = :key";
		
		$tempStmt = $this->conn->prepare($tempSQL);
		$tempStmt->bindValue("key", $this->InfractionKind);
		$tempStmt->execute();
		$tempStmt->getWrappedStatement()->setFetchMode(\PDO::FETCH_OBJ);
		$TicketType = $tempStmt->fetch(\PDO::FETCH_OBJ);		
		$TicketType = $TicketType->TicketType;
		
		if($this->Type == 'driver'){
			$sql = "INSERT INTO DriverTicket
					(TicketNumber, BadgeNumber, LicenseNumber, TicketType,Longitude,Latitude,Time)
					VALUES
					(:ticket,:badge,:license,:type,:longitude,:latitude,:time)";
					
			$this->createStmt = $this->conn->prepare($sql);
			
			$this->createStmt->bindValue("ticket", $this->TicketNumber);
			$this->createStmt->bindValue("badge", $this->Officer);			
			$this->createStmt->bindValue("license", $this->Owner);
			$this->createStmt->bindValue("type", $TicketType[0]);
			$this->createStmt->bindValue("longitude", $this->Longitude);
			$this->createStmt->bindValue("latitude", $this->Latitude);
			$this->createStmt->bindValue("time", $this->Time);
		}
		elseif($this->Type == 'vehicle'){
			$sql = "INSERT INTO VehicleTicket
					(TicketNumber, BadgeNumber, VIN, TicketType,Longitude,Latitude,Time)
					VALUES
					(:ticket,:badge,:license,:type,:longitude,:latitude,:time)";
			
			$this->createStmt = $this->conn->prepare($sql);
			
			
			$vinSQL = "SELECT
							VIN
						FROM Vehicle ";
						
			if(preg_match('/^[a-zA-Z0-9]{10}$/', $this->Owner) > 0){
				//This is RFID key
				$vinSQL .= " WHERE RfidKey = :key";
			}
			elseif(preg_match('/^[a-zA-Z]{3}-[0-9]{3}$/', $this->Owner) > 0){
				//This is License Plate
				$vinSQL .= " WHERE LicensePlate = :key";
			}
			$vinStmt = $this->conn->prepare($vinSQL);
			$vinStmt->bindValue("key", $this->Owner);
			$vinStmt->execute();
			$vinStmt->getWrappedStatement()->setFetchMode(\PDO::FETCH_OBJ);
			$this->Owner = $vinStmt->fetch(\PDO::FETCH_OBJ)->VIN;
						
			$this->createStmt->bindValue("ticket", $this->TicketNumber);
			$this->createStmt->bindValue("badge", $this->Officer);			
			$this->createStmt->bindValue("license", $this->Owner);
			$this->createStmt->bindValue("type", $TicketType[0]);
			$this->createStmt->bindValue("longitude", $this->Longitude);
			$this->createStmt->bindValue("latitude", $this->Latitude);
			$this->createStmt->bindValue("time", $this->Time);
		}
		
	}
	
	/**
	* Method to be called in create() to create observations in the database
	*/
	public function createObservations(){
		//Verify it has observations...
		if(!empty($this->Observations)){
			
			//Load observation ids.	
			$sql = "SELECT 
						idObservationsType
					FROM ObservationsType ";

			foreach($this->Observations as $key=>$oID){
				if($key == 0){
					$sql .= " WHERE Observations = :obs$key ";
				}
				else {
					$sql .= "";
				}
				$sql .= " OR Observations = :obs$key ";
			}

			$loadStmt = $this->conn->prepare($sql);

			foreach($this->Observations as $key=>$oID){
				$loadStmt->bindValue("obs$key", $oID);
			}

			$loadStmt->execute();

			$obsIds = $loadStmt->fetchAll(\PDO::FETCH_NUM);

			//Create!		
			$sql = "INSERT INTO Observations
					(TicketNumber, ObservationsType)
					VALUES ";

			foreach($obsIds as $key=>$oID){
				if($key == 0){
					$sql .= " (:ticket,:obsID$key) ";
				}
				else {
					$sql .= ", (:ticket,:obsID$key) ";
				}
			}

			$createStmt = $this->conn->prepare($sql);

			$createStmt->bindValue("ticket", $this->TicketNumber);
			foreach($obsIds as $key=>$oID){			
				$createStmt->bindValue("obsID$key", $oID[0]);
			}			
						
			$createStmt->execute();	
		}
	}
	
	/**
	* Overwrites parent method only to call createObservations
	*/
	public function create(){
		if(is_null($this->createStmt)){
			$this->prepareCreateQuery();
		}		
		
		$this->conn->beginTransaction();
		try{
		    // do stuff
			$this->createStmt->execute();
			
			if($this->Type == 'driver'){
				$this->createObservations();
			}
			
		    $this->conn->commit();
			
			
			// $this->isLoaded = true;
			
			//Return acknowledge
			return $this->sendAck('Created succesfully!');
			
			
		} catch(\Exception $e) {
		    $this->conn->rollback();
			//return error message
			return $this->sendErr('Something went wrong: ' . $e->getMessage());	
		}
	}
		
	public function prepareDeleteQuery(){
		if($this->getType() == 'driver'){
			$sql = "DELETE FROM DriverTicket
			    	WHERE TicketNumber = :key";
		}
		elseif($this->getType() == 'vehicle'){
			$sql = "DELETE FROM VehicleTicket
			    	WHERE TicketNumber = :key";
		}
		
		$this->deleteStmt = $this->conn->prepare($sql);

		$this->deleteStmt->bindValue("key", $this->TicketNumber);
	}
	
	/**
	* Method to be called in delete() to delete observations in the database
	*/
	public function deleteObservations(){
		$sql = "DELETE FROM Observations
		    	WHERE TicketNumber = :key";
		
		$deleteStmt = $this->conn->prepare($sql);		
		$deleteStmt->bindValue("key", $this->TicketNumber);
		
		$deleteStmt->execute();
	}
	
	/**
	* Overwrites parent method only to call deleteObservations
	*/
	public function delete(){
		if(is_null($this->deleteStmt)){
			$this->prepareDeleteQuery();
		}

		$this->conn->beginTransaction();
		try{
		    // do stuff
			if($this->Type == 'driver'){
				$this->deleteObservations();
			}
		
			$this->deleteStmt->execute();
			
		    $this->conn->commit();
						
			//Return acknowledge
			return $this->sendAck('Deleted succesfully!');
						
		} catch(\Exception $e) {
		    $this->conn->rollback();
			//return error message
			return $this->sendErr('Something went wrong: ' . $e->getMessage());	
		}	
	}
	
	public function toXML(\SimpleXMLElement &$xml=null) {
		$return = false;
		if(is_null($xml)){
			$xml = new \SimpleXMLElement('<?xml version="1.0" encoding="utf-8" ?><xmlresponse></xmlresponse>');
			$return = true;
		}
		
		$ticketXML = $xml->addChild('ticket');
		
		$ticketXML->addChild('ticketNumber',$this->getTicketNumber());
		$ticketXML->addChild('latitude',$this->getLatitude());
		$ticketXML->addChild('longitude',$this->getLongitude());
		$ticketXML->addChild('time',$this->getTime());
		$ticketXML->addChild('owner',$this->getOwner());
		$ticketXML->addChild('infraction',$this->getInfractionKind());
		$ticketXML->addChild('fine',$this->getFine());
		$ticketXML->addChild('articleNumber',$this->getArticle_Number());
		$ticketXML->addChild('point',$this->getPoint());
		$ticketXML->addChild('type',$this->getType());
		$ticketXML->addChild('officer',$this->getOfficer());
		
		if($this->getType() == 'driver'){
			$obsXML = $ticketXML->addChild('observations');
			$os = $this->getObservations();
			foreach($os as $o){
				$obsXML->addChild('observation',$o[0]);
			}
		}
		
		if($return){		
			return $xml->asXML();
		}
	}
}